const { query, transaction, formatRows, buildWhereClause } = require('../config/database');
const bcrypt = require('bcryptjs');

class User {
  // 创建用户
  static async create(userData) {
    const { username, password, email, nickname, phone } = userData;
    
    // 检查用户名是否已存在
    const existingUser = await this.findByUsername(username);
    if (existingUser) {
      throw new Error('用户名已存在');
    }
    
    // 检查邮箱是否已存在
    if (email) {
      const existingEmail = await this.findByEmail(email);
      if (existingEmail) {
        throw new Error('邮箱已存在');
      }
    }
    
    // 加密密码
    const passwordHash = await bcrypt.hash(password, 10);
    
    const sql = `
      INSERT INTO users (username, password_hash, email, nickname, phone)
      VALUES (?, ?, ?, ?, ?)
    `;
    
    const result = await query(sql, [username, passwordHash, email, nickname, phone]);
    
    // 返回创建的用户信息（不包含密码）
    return await this.findById(result.insertId);
  }
  
  // 根据ID查找用户
  static async findById(id) {
    const sql = `
      SELECT id, username, email, nickname, avatar_url, phone, status, 
             created_at, updated_at, last_login_at
      FROM users 
      WHERE id = ? AND status != 'banned'
    `;
    
    const rows = await query(sql, [id]);
    return rows.length > 0 ? formatRows(rows)[0] : null;
  }
  
  // 根据用户名查找用户
  static async findByUsername(username) {
    const sql = `
      SELECT id, username, email, nickname, avatar_url, phone, status,
             created_at, updated_at, last_login_at
      FROM users 
      WHERE username = ? AND status != 'banned'
    `;
    
    const rows = await query(sql, [username]);
    return rows.length > 0 ? formatRows(rows)[0] : null;
  }
  
  // 根据邮箱查找用户
  static async findByEmail(email) {
    const sql = `
      SELECT id, username, email, nickname, avatar_url, phone, status,
             created_at, updated_at, last_login_at
      FROM users 
      WHERE email = ? AND status != 'banned'
    `;
    
    const rows = await query(sql, [email]);
    return rows.length > 0 ? formatRows(rows)[0] : null;
  }
  
  // 验证用户登录
  static async validateLogin(username, password) {
    const sql = `
      SELECT id, username, password_hash, email, nickname, avatar_url, 
             phone, status, created_at, updated_at, last_login_at
      FROM users 
      WHERE username = ? AND status = 'active'
    `;
    
    const rows = await query(sql, [username]);
    if (rows.length === 0) {
      return null;
    }
    
    const user = rows[0];
    const isValidPassword = await bcrypt.compare(password, user.password_hash);
    
    if (!isValidPassword) {
      return null;
    }
    
    // 更新最后登录时间
    await this.updateLastLogin(user.id);
    
    // 返回用户信息（不包含密码哈希）
    const { password_hash, ...userInfo } = user;
    return formatRows([userInfo])[0];
  }
  
  // 更新最后登录时间
  static async updateLastLogin(userId) {
    const sql = `UPDATE users SET last_login_at = CURRENT_TIMESTAMP WHERE id = ?`;
    await query(sql, [userId]);
  }
  
  // 更新用户信息
  static async update(userId, updateData) {
    const allowedFields = ['email', 'nickname', 'avatar_url', 'phone'];
    const updates = [];
    const params = [];
    
    for (const [key, value] of Object.entries(updateData)) {
      if (allowedFields.includes(key) && value !== undefined) {
        updates.push(`${key} = ?`);
        params.push(value);
      }
    }
    
    if (updates.length === 0) {
      throw new Error('没有有效的更新字段');
    }
    
    params.push(userId);
    
    const sql = `
      UPDATE users 
      SET ${updates.join(', ')}, updated_at = CURRENT_TIMESTAMP
      WHERE id = ?
    `;
    
    await query(sql, params);
    return await this.findById(userId);
  }
  
  // 修改密码
  static async changePassword(userId, oldPassword, newPassword) {
    // 验证旧密码
    const sql = `SELECT password_hash FROM users WHERE id = ?`;
    const rows = await query(sql, [userId]);
    
    if (rows.length === 0) {
      throw new Error('用户不存在');
    }
    
    const isValidOldPassword = await bcrypt.compare(oldPassword, rows[0].password_hash);
    if (!isValidOldPassword) {
      throw new Error('原密码错误');
    }
    
    // 更新密码
    const newPasswordHash = await bcrypt.hash(newPassword, 10);
    const updateSql = `
      UPDATE users 
      SET password_hash = ?, updated_at = CURRENT_TIMESTAMP
      WHERE id = ?
    `;
    
    await query(updateSql, [newPasswordHash, userId]);
    return true;
  }
  
  // 获取用户列表（管理员功能）
  static async getList(conditions = {}, page = 1, limit = 10) {
    const { clause, params } = buildWhereClause(conditions);
    
    const countSql = `SELECT COUNT(*) as total FROM users ${clause}`;
    const countResult = await query(countSql, params);
    const total = countResult[0].total;
    
    const offset = (page - 1) * limit;
    const listSql = `
      SELECT id, username, email, nickname, avatar_url, phone, status,
             created_at, updated_at, last_login_at
      FROM users 
      ${clause}
      ORDER BY created_at DESC
      LIMIT ${limit} OFFSET ${offset}
    `;
    
    const rows = await query(listSql, params);
    
    return {
      data: formatRows(rows),
      pagination: {
        page,
        limit,
        total,
        pages: Math.ceil(total / limit)
      }
    };
  }
  
  // 删除用户（软删除）
  static async delete(userId) {
    const sql = `
      UPDATE users 
      SET status = 'inactive', updated_at = CURRENT_TIMESTAMP
      WHERE id = ?
    `;
    
    await query(sql, [userId]);
    return true;
  }
}

module.exports = User;
